package com.kaikeba.dao.impl;

import com.kaikeba.bean.User;
import com.kaikeba.dao.UserDao;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.*;

public class UserDaoImpl implements UserDao {

    /**
     * 用户返回控制台中所需的数据
     * {（用戶人數）u_sum：xxx,（日注册量）u_day：xxx}
     *
     * @return
     */
    @Override
    public Map<String, Integer> console() {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        Map<String,Integer> map = new HashMap<>();
        try {
             state = conn.prepareStatement("select \n" +
                     "COUNT(id) u_sum,COUNT(TO_DAYS(registertime)=TO_DAYS(NOW()) or null) u_day\n" +
                     "from user");
            resultSet = state.executeQuery();
            while (resultSet.next()){
                int u_sum = resultSet.getInt("u_sum");
                int u_day = resultSet.getInt("u_day");
                map.put("u_sum",u_sum);
                map.put("u_day",u_day);
            }
            return map;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,resultSet);
        }
        return null;
    }

    /**
     * 查找全部快递并根据参数判断是否分页
     *
     * @param limit      是否分页 true（默认）分页  false 不分页
     * @param offset     起始索引
     * @param pageNumber 单页显示数量
     * @return
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {

        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        List<User> list = new ArrayList<>();
        try {
            if (limit){
                //分页
                state = conn.prepareStatement("select * from user limit ?,?");
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                //不分页
                state = conn.prepareStatement("select * from user");
            }
            resultSet = state.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("username");
                String userPhone = resultSet.getString("userphone");
                String password = resultSet.getString("password");
                String identityNumber = resultSet.getString("identitynumber");
                Timestamp registerTime = resultSet.getTimestamp("registertime");
                Timestamp loginTime = resultSet.getTimestamp("logintime");
                User user = new User(id,userName,userPhone,password,registerTime,loginTime);
                user.setIdentityNumber(identityNumber);
                list.add(user);
            }
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,resultSet);
        }

        return null;
    }

    /**
     * 根据用户电话号码查询快递信息
     *
     * @param userPhone 用户电话号码
     * @return
     */
    @Override
    public List<User> findByUserPhone(String userPhone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        List<User> list = new ArrayList<>();
        try {
            state = conn.prepareStatement("select * from user where userphone=?");
            state.setString(1,userPhone);
            resultSet = state.executeQuery();

            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("username");
                String userphone = resultSet.getString("userphone");
                String identityNumber = resultSet.getString("identitynumber");
                String password = resultSet.getString("password");
                User user = new User(userName,userPhone,identityNumber,password);
                user.setId(id);
                list.add(user);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,resultSet);
        }

        return null;
    }

    /**
     * 录入
     *
     * @param u 录入的用户对象
     * @return 录入的结果，true表示成功，false表示失败
     */
    @Override
    public boolean insert(User u) {

        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            String userName = u.getUserName();
            String userPhone = u.getUserPhone();
            String identityNumber = u.getIdentityNumber();
            String password = u.getPassword();

            state = conn.prepareStatement("insert into user values (null ,?,?,?,?,now(),null )");
            state.setString(1,userName);
            state.setString(2,userPhone);
            state.setString(3,identityNumber);
            state.setString(4,password);

            int i = state.executeUpdate();
            return i>0;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用户修改
     *
     * @param id      要修改的用户id
     * @param newUser 新的用户对象（userName，userPhone,identityNumber,password）
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean update(int id, User newUser) {

        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            String userName = newUser.getUserName();
            String userPhone = newUser.getUserPhone();
            String identityNumber = newUser.getIdentityNumber();
            String password = newUser.getPassword();

           state = conn.prepareStatement("update user set username=?,userphone=?,identitynumber=?,password=?\n" +
                   "where id =?;");
           state.setString(1,userName);
           state.setString(2,userPhone);
           state.setString(3,identityNumber);
           state.setString(4,password);
           state.setInt(5,id);

            int i = state.executeUpdate();
            return i>0;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 删除
     * @param id 需要删除的用户id
     * @return
     */
    @Override
    public boolean delete(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
           state = conn.prepareStatement("delete from user where id=? ");
           state.setInt(1,id);
           int i = state.executeUpdate();
           return i>0;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用于微信端用户的登录
     *
     * @param userPhone 用户手机号码
     * @param password  密码
     * @return
     */
    @Override
    public User login(String userPhone, String password) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;

        try {
            state = conn.prepareStatement("select * from user where userphone=? and password=?");
            state.setString(1,userPhone);
            state.setString(2,password);
            resultSet = state.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("username");
                String userPhone1 = resultSet.getString("userphone");
                String password1 = resultSet.getString("password");
                String identityNumber = resultSet.getString("identitynumber");
                Timestamp registerTime = resultSet.getTimestamp("registertime");
                Timestamp loginTime = resultSet.getTimestamp("logintime");
                User user = new User(id,userName,userPhone,password,registerTime,loginTime);
                user.setIdentityNumber(identityNumber);
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,resultSet);
        }
        return null;
    }
}
